"""
Case Type   : ustore-Consistency
Case Name   : 创建所有需要表 
Create At   : 2023/02/10
Owner       : @zou_jialiang050
Description :
    1.建schema1
    2.建表1并插入数据
    3.建表2并插入数据
    4.建schema2
    5.建表3
    6.建存储过程并调用
    7.建表4并插入数据
    8.建表5并插入数据
    9.建表6并插入数据,为表6创建索引
    10.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.成功
History     :
    modified by l1azzzy 2025/1/23 增加线程等待时间
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class CONSISTENCY(unittest.TestCase):
    def setUp(self):
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.schema1 = 's_ustore_consistency_01'
        self.schema2 = 's_ustore_consistency_02'
        self.table1 = 't_ustore_consistency_0001_01'
        self.table2 = 't_ustore_consistency_0001_02'
        self.table3 = 't_ustore_consistency_0001_03'
        self.table4 = 't_ustore_consistency_0001_04'
        self.table5 = 't_ustore_consistency_0001_05'
        self.table6 = 't_ustore_consistency_0001_06'
        self.procedure = 'p_ustore_consistency_0001'
        self.index = 'i_ustore_consistency_0001'

    def test_ustore_consistency(self):
        text1 = '-----step1:建schema1 expect:成功-----'
        self.log.info(text1)
        create_schema = self.pri_sh.execut_db_sql(
            f'''drop schema if exists {self.schema1} cascade;
            create schema {self.schema1};''')
        self.log.info(create_schema)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, create_schema,
                      '执行失败' + text1)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, create_schema,
                      '执行失败' + text1)

        text2 = 'step2:建表1并插入数据 expect:成功'
        self.log.info(text2)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema1}.{self.table1};
            create table {self.schema1}.{self.table1}(c_id int,c_d_id int 
            not null,c_w_id int not null,c_first varchar(16) not null,
            c_middle char(2),c_last varchar(16) not null,c_street_1 
            varchar(20) not null,c_street_2 varchar(20),c_city varchar(20) 
            not null,c_state char(2) not null,c_zip char(9) not null,
            c_phone char(16) not null,c_since timestamp,c_credit char(2) 
            not null,c_credit_lim numeric(12,2),c_discount numeric(4,4),
            c_balance numeric(12,2),c_ytd_payment real not null,
            c_payment_cnt number not null,c_delivery_cnt bool not null,
            c_end date not null,c_vchar varchar(1000),c_data clob,c_text 
            blob,primary key (c_id,c_d_id,c_w_id)) with 
            (storage_type=ustore);''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text2)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text2)
        insert_res = self.pri_sh.execut_db_sql(
            f"insert into {self.schema1}.{self.table1} values"
            f"(1,1,1,'is'||'1'||'cmRDs'||'1','OE','BAR'||'1'||'Bar'||'1',"
            f"'bkili'||'1'||'fcrRGF'||'1','pmbwo'||'1'||'vhSDGJ'||'1','dyf'"
            f"||'1'||'rDa'||'1','uq',4801||'1',940||'1'||215||'1',"
            f"to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'GC',50001.0,"
            f"0.4361328,-10.0,10.0,1,true,to_char(sysdate,"
            f"'yyyy-mm-dd hh24:mi:ss'),'QVLDETANRBRBURBMfhlhfrcllgfSMNTECC34"
            f"8493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMfhlhfrcllgfSMNTE"
            f"CC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMfhlhfrcllgfS"
            f"MNTECC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMfhlhfrc"
            f"llgfSMNTECC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMf"
            f"hlhfrcllgfSMNTECC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRB"
            f"URBMfhlhfrcllgfSMNTECC348493214893542NPFZCSfjlufvCDSF'||'1',"
            f"'QVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSCD"
            f"SFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSC"
            f"DSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYS"
            f"CDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXY"
            f"SCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXX"
            f"YSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSY"
            f"KXXYSCDSF'||'1','123435458764312345521344565672312342455456677"
            f"6763221132454566768767433242323445453565654542323');"
            f"begin "
                f"for i in 1..1000 LOOP "
                    f"insert into {self.schema1}.{self.table1} select "
                    f"c_id+i,c_d_id+i,c_w_id+i,'isi'||i,c_middle,'BAR'||i,"
                    f"c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,"
                    f"c_since,c_credit,c_credit_lim,c_discount,c_balance,"
                    f"c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_end,"
                    f"c_vchar,c_data,c_text from "
                    f"{self.schema1}.{self.table1} where c_id=1;"
                f"end loop;"
            f"end;")
        self.log.info(insert_res)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG,
                      insert_res, '执行失败' + text2)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res, '执行失败'
                      + text2)

        text3 = 'step2:建表2并插入数据 expect:成功'
        self.log.info(text3)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema1}.{self.table2};
            create table {self.schema1}.{self.table2}(c_clob1 clob,c_clob2 
            clob,c_blob1 blob,c_blob2 blob);''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text3)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text3)
        insert_res = self.pri_sh.execut_db_sql(
            f"insert into {self.schema1}.{self.table2} values("
            f"'QVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXY"
            f"SCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKX"
            f"XYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCS"
            f"YKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPF"
            f"ZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvN"
            f"PFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkg"
            f"vNPFZCSYKXXYSCDSF1QVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfc"
            f"mkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvd"
            f"fcmkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmg"
            f"fvdfcmkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjg"
            f"fvmgfvdfcmkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMj"
            f"jjjgfvmgfvdfcmkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGG"
            f"SMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSCDSF1','SYKXXYSCDSFQVLDETAN"
            f"RBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSCDSFQVLD"
            f"ETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSCDSF"
            f"QVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSC"
            f"DSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXY"
            f"SCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYK"
            f"XXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZC',"
            f"'12343545876431234552134456567231234245545667767632211324545"
            f"667687674332423234454535656545423231234354587643123455213445"
            f"656723123424554566776763221132454566768767433242323445453565"
            f"654542323','324545667687674332423234454535656545423231234354"
            f"5876431234552134456567231234245545667767632211');")
        self.log.info(insert_res)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res,
                      '执行失败' + text3)

        text4 = 'step4:建schema2 expect:成功'
        self.log.info(text4)
        create_schema = self.pri_sh.execut_db_sql(
            f'''drop schema if exists {self.schema2} cascade;
            create schema {self.schema2};''')
        self.log.info(create_schema)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, create_schema,
                      '执行失败' + text4)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, create_schema,
                      '执行失败' + text4)

        text5 = 'step5:建表3 expect:成功'
        self.log.info(text5)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema2}.{self.table3};
            create table {self.schema2}.{self.table3}(c_int int) with 
            (storage_type=ustore);''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text5)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text5)

        text6 = 'step6:建存储过程并调用 expect:成功'
        self.log.info(text6)
        create_procedure = self.pri_sh.execut_db_sql(
            f'''create or replace procedure {self.schema2}.{self.procedure}() 
            is
             b int := 0;
                begin
                    for i in 1..1000 LOOP
                        insert into {self.schema2}.{self.table3} values(i);
                    end loop;
                end;''')
        self.log.info(create_procedure)
        call_procedure = self.pri_sh.execut_db_sql(
            f'''call {self.schema2}.{self.procedure}();
            select count(*) from {self.schema2}.{self.table3};''')
        self.log.info(call_procedure)
        self.assertIn('1000', call_procedure, '执行失败' + text6)

        text7 = 'step7:建表4并插入数据 expect:成功'
        self.log.info(text7)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema2}.{self.table4};
            create table {self.schema2}.{self.table4}(c_id int,c_d_id int 
            not null,c_w_id int not null,c_first varchar(16) not null,
            c_middle char(2),c_last varchar(16) not null,
            c_street_1 varchar(20) not null,c_street_2 varchar(20),
            c_city varchar(20) not null,c_state char(2) not null,
            c_zip char(9) not null,c_phone char(16) not null,
            c_since timestamp,c_credit char(2) not null,c_credit_lim number,
            c_discount number,c_balance number,c_ytd_payment number not null,
            c_payment_cnt int not null,c_delivery_cnt int not null,
            c_data clob not null) with (storage_type=ustore);

            insert into {self.schema2}.{self.table4} values (1,1,1,'aaaaa',
            'ab','dafkldafkadbfeod','dafkldafkadbfeodefgh',
            'dafkldafkadbfeodefgh','dafkldafkadbfeodefgh','xy','acgfdagfs',
            'dafkldafkadbfeod',null,'ad',50000.0,0.4491328,-10.0,10.0,1,0,
            lpad('qraogfdamjkfdlhgmdjf',4000,'qraogfdamjkfdlhgmdjf'));
            insert into {self.schema2}.{self.table4} select c_id+1,c_d_id+1,
            c_w_id+1,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+2,c_d_id+2,
            c_w_id+2,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+4,c_d_id+4,
            c_w_id+4,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+8,c_d_id+8,
            c_w_id+8,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+16,c_d_id+16,
            c_w_id+16,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+32,c_d_id+32,
            c_w_id+32,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+64,c_d_id+64,
            c_w_id+64,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+128,
            c_d_id+128,c_w_id+128,c_first,c_middle,c_last,c_street_1,
            c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,
            c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,
            c_delivery_cnt,c_data from {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+256,
            c_d_id+256,c_w_id+256,c_first,c_middle,c_last,c_street_1,
            c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,
            c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,
            c_delivery_cnt,c_data from {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+512,
            c_d_id+512,c_w_id+512,c_first,c_middle,c_last,c_street_1,
            c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,
            c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,
            c_delivery_cnt,c_data from {self.schema2}.{self.table4};
            delete from {self.schema2}.{self.table4} where c_id>1000;''')    
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text7)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text7)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, create_table,
                      '执行失败' + text7)
        self.assertEqual(create_table.count(self.constant.INSERT_SUCCESS_MSG), 
                         11, '执行失败' + text7)

        text8 = 'step8:建表5并插入数据 expect:成功'
        self.log.info(text8)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema2}.{self.table5};
            create table {self.schema2}.{self.table5}(c_id int,c_d_id int not 
            null,c_w_id int not null,c_first varchar(200) not null,c_middle 
            char(2),c_last varchar(16) not null,c_street_1 varchar(20) 
            not null,c_street_2 varchar(20),c_city varchar(20) not null,
            c_state char(2) not null,c_zip char(9) not null,c_phone char(16) 
            not null,c_since timestamp,c_credit char(2) not null,c_credit_lim 
            number,c_discount number,c_balance number,c_ytd_payment number not 
            null,c_payment_cnt int not null,c_delivery_cnt int not null,c_data 
            clob not null) with (storage_type=ustore);
            insert into {self.schema2}.{self.table5} select c_int,c_int,c_int,
            'aaaaa','ab','dafkldafkadbfeod','dafkldafkadbfeodefgh',
            'dafkldafkadbfeodefgh','dafkldafkadbfeodefgh','xy','acgfdagfs',
            'dafkldafkadbfeod',null,'ad',50000.0,0.4491328,-10.0,10.0,1,0,
            lpad('qraogfdamjkfdlhgmdjf',4000,'qraogfdamjkfdlhgmdjf') from 
            {self.schema2}.{self.table3};''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text8)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text8)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table,
                      '执行失败' + text8)

        text9 = 'step9:建表6并插入数据,为表6创建索引 expect:成功'
        self.log.info(text9)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema2}.{self.table6};
            create table {self.schema2}.{self.table6}(c_id int,c_d_id int not 
            null,c_w_id int not null,c_first varchar(200) not null,
            c_middle char(2),c_last varchar(16) not null,c_street_1 
            varchar(20) not null,c_street_2 varchar(20),c_city varchar(20) 
            not null,c_state char(2) not null,c_zip char(9) not null,c_phone 
            char(16) not null,c_since timestamp,c_credit char(2) not null,
            c_credit_lim number,c_discount number,c_balance number,
            c_ytd_payment number not null,c_payment_cnt int not null,
            c_delivery_cnt int not null,c_data clob not null) with 
            (storage_type=ustore);
            drop index if exists {self.schema2}.{self.index};
            create unique index {self.schema2}.{self.index} on 
            {self.schema2}.{self.table6} (c_id,c_d_id,c_w_id);
            insert into {self.schema2}.{self.table6} select c_int,c_int,c_int,
            'aaaaa','ab','dafkldafkadbfeod','dafkldafkadbfeodefgh',
            'dafkldafkadbfeodefgh','dafkldafkadbfeodefgh','xy','acgfdagfs',
            'dafkldafkadbfeod',null,'ad',50000.0,0.4491328,-10.0,10.0,1,0,
            lpad('qraogfdamjkfdlhgmdjf',4000,'qraogfdamjkfdlhgmdjf') from 
            {self.schema2}.{self.table3};''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text9)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text9)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table,
                      '执行失败' + text9)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG, create_table,
                      '执行失败' + text9)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, create_table,
                      '执行失败' + text9)

    def tearDown(self):
        text10 = '--------无需清理环境--------'
        self.log.info(text10)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
